﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Common;
using System.Data;

namespace Calvin.Utilities
{
    public class DbHelper
    {

        private static DbProviderFactory _factory;
        public string ConnectionString
        {
            get;
            set;
        }

        public string ProviderName
        {
            get;
            set;
        }
        public DbHelper(string connectionString, string providername)
        {
            this.ConnectionString = connectionString;
            this.ProviderName = providername;
            CreateFactory();


        }
        public DbConnection GetConnection()
        {
            DbConnection conn = _factory.CreateConnection();
            conn.ConnectionString = this.ConnectionString;
            conn.Open();
            return conn;
        }

        public void RecycleConnection(DbConnection connection)
        {
            if (connection == null)
            {
                return;
            }
            connection.Close();
            connection.Dispose();
        }
        public DbCommand GetCommand(DbConnection connection,string commandText,CommandType commandType,DbTransaction tran)
        {
            DbCommand _comm=connection.CreateCommand();
            _comm.CommandText=commandText;
            _comm.CommandType=commandType;
            _comm.Transaction=tran;
            return _comm;
        }
        


        public void CreateFactory()
        {
            _factory = DbProviderFactories.GetFactory(ProviderName);
        }
        #region ExecuteDataSet
        public DataSet ExecuteDataSet(string sql)
        {
            DataSet set;
            DbConnection connection = this.GetConnection();
            try
            {
                set = ExecuteDataSet(sql, CommandType.Text, connection, null, null);
            }
            finally
            {
                this.RecycleConnection(connection);
            }
            return set;
        }

        public DataSet ExecuteDataSet(string sql, DbConnection connection)
        {
            return ExecuteDataSet(sql, CommandType.Text, connection, null, null);
        }

        public DataSet ExecuteDataSet(string sql, CommandType commandType)
        {
            DataSet set;
            DbConnection connection = this.GetConnection();
            try
            {
                set = ExecuteDataSet(sql, commandType, connection, null, null);
            }
            finally
            {
                this.RecycleConnection(connection);
            }
            return set;
        }

        public DataSet ExecuteDataSet(string sql, IsolationLevel iso)
        {
            DataSet set2;
            DbConnection connection = this.GetConnection();
            DbTransaction transaction = null;
            try
            {
                transaction = connection.BeginTransaction(iso);
                DataSet set = ExecuteDataSet(sql, CommandType.Text, connection, transaction, null);
                transaction.Commit();
                set2 = set;
            }
            catch
            {
                if (transaction != null)
                {
                    transaction.Rollback();
                }
                throw;
            }
            finally
            {
                this.RecycleConnection(connection);
            }
            return set2;
        }

        public DataSet ExecuteDataSet(string sql, DbParameter[] parameters)
        {
            DataSet set;
            DbConnection connection = this.GetConnection();
            try
            {
                set = ExecuteDataSet(sql, CommandType.Text, connection, null, parameters);
            }
            finally
            {
                this.RecycleConnection(connection);
            }
            return set;
        }

        public DataSet ExecuteDataSet(string sql, DbConnection connection, DbTransaction transaction)
        {
            return ExecuteDataSet(sql, CommandType.Text, connection, transaction, null);
        }

        public DataSet ExecuteDataSet(string sql, DbConnection connection, DbParameter[] parameters)
        {
            return ExecuteDataSet(sql, CommandType.Text, connection, null, parameters);
        }

        public DataSet ExecuteDataSet(string sql, CommandType commandType, DbConnection connection)
        {
            return ExecuteDataSet(sql, commandType, connection, null, null);
        }

        public DataSet ExecuteDataSet(string sql, CommandType commandType, IsolationLevel iso)
        {
            DataSet set2;
            DbConnection connection = this.GetConnection();
            DbTransaction transaction = null;
            try
            {
                transaction = connection.BeginTransaction(iso);
                DataSet set = ExecuteDataSet(sql, commandType, connection, transaction, null);
                transaction.Commit();
                set2 = set;
            }
            catch
            {
                if (transaction != null)
                {
                    transaction.Rollback();
                }
                throw;
            }
            finally
            {
                this.RecycleConnection(connection);
            }
            return set2;
        }

        public DataSet ExecuteDataSet(string sql, CommandType commandType, DbParameter[] parameters)
        {
            DataSet set;
            DbConnection connection = this.GetConnection();
            try
            {
                set = ExecuteDataSet(sql, commandType, connection, null, parameters);
            }
            finally
            {
                this.RecycleConnection(connection);
            }
            return set;
        }

        public DataSet ExecuteDataSet(string sql, DbParameter[] parameters, IsolationLevel iso)
        {
            DataSet set2;
            DbConnection connection = this.GetConnection();
            DbTransaction transaction = null;
            try
            {
                transaction = connection.BeginTransaction(iso);
                DataSet set = ExecuteDataSet(sql, CommandType.Text, connection, transaction, parameters);
                transaction.Commit();
                set2 = set;
            }
            catch
            {
                if (transaction != null)
                {
                    transaction.Rollback();
                }
                throw;
            }
            finally
            {
                this.RecycleConnection(connection);
            }
            return set2;
        }

        public DataSet ExecuteDataSet(string sql, DbConnection connection, DbTransaction transaction, DbParameter[] parameters)
        {
            return ExecuteDataSet(sql, CommandType.Text, connection, transaction, parameters);
        }

        public DataSet ExecuteDataSet(string sql, CommandType commandType, DbConnection connection, DbTransaction transaction)
        {
            return ExecuteDataSet(sql, commandType, connection, transaction, null);
        }

        public DataSet ExecuteDataSet(string sql, CommandType commandType, DbConnection connection, DbParameter[] parameters)
        {
            return ExecuteDataSet(sql, commandType, connection, null, parameters);
        }

        public DataSet ExecuteDataSet(string sql, CommandType commandType, DbParameter [] parameters, IsolationLevel iso)
        {
            DataSet set2;
            DbConnection connection = this.GetConnection();
            DbTransaction transaction = null;
            try
            {
                transaction = connection.BeginTransaction(iso);
                DataSet set = ExecuteDataSet(sql, commandType, connection, transaction, parameters);
                transaction.Commit();
                set2 = set;
            }
            catch
            {
                if (transaction != null)
                {
                    transaction.Rollback();
                }
                throw;
            }
            finally
            {
                this.RecycleConnection(connection);
            }
            return set2;
        }

        public DataSet ExecuteDataSet(string sql, CommandType commandType, DbConnection connection, DbTransaction transaction, DbParameter [] parameters)
        {

            DbCommand selectCommand = GetCommand(connection, sql, commandType, transaction);
            if ((parameters != null) && (parameters.Length > 0))
            {
                selectCommand.Parameters.AddRange(parameters);
            }
            DbDataAdapter adapter = _factory.CreateDataAdapter();
            DataSet dataSet = new DataSet();
            adapter.Fill(dataSet);
            return dataSet;
        }
        #endregion
        #region  ExecuteNonQuery
        public int ExecuteNonQuery(string sql)
        {
            int num;
            DbConnection connection = this.GetConnection();
            try
            {
                num = ExecuteNonQuery(sql, CommandType.Text, connection, null, null);
            }
            finally
            {
                this.RecycleConnection(connection);
            }
            return num;
        }

        public int ExecuteNonQuery(string sql, DbConnection connection)
        {
            return ExecuteNonQuery(sql, CommandType.Text, connection, null, null);
        }

        public int ExecuteNonQuery(string sql, CommandType commandType)
        {
            int num;
            DbConnection connection = this.GetConnection();
            try
            {
                num = ExecuteNonQuery(sql, commandType, connection, null, null);
            }
            finally
            {
                this.RecycleConnection(connection);
            }
            return num;
        }

        public int ExecuteNonQuery(string sql, IsolationLevel iso)
        {
            int num2;
            DbConnection connection = this.GetConnection();
            DbTransaction transaction = null;
            try
            {
                transaction = connection.BeginTransaction(iso);
                int num = ExecuteNonQuery(sql, CommandType.Text, connection, transaction, null);
                transaction.Commit();
                num2 = num;
            }
            catch
            {
                if (transaction != null)
                {
                    transaction.Rollback();
                }
                throw;
            }
            finally
            {
                this.RecycleConnection(connection);
            }
            return num2;
        }

        public int ExecuteNonQuery(string sql, DbParameter[] parameters)
        {
            int num;
            DbConnection connection = this.GetConnection();
            try
            {
                num = ExecuteNonQuery(sql, CommandType.Text, connection, null, parameters);
            }
            finally
            {
                this.RecycleConnection(connection);
            }
            return num;
        }

        public int ExecuteNonQuery(string sql, DbConnection connection, DbTransaction transaction)
        {
            return ExecuteNonQuery(sql, CommandType.Text, connection, transaction, null);
        }

        public int ExecuteNonQuery(string sql, DbConnection connection, DbParameter[] parameters)
        {
            return ExecuteNonQuery(sql, CommandType.Text, connection, null, parameters);
        }

        public int ExecuteNonQuery(string sql, CommandType commandType, DbConnection connection)
        {
            return ExecuteNonQuery(sql, commandType, connection, null, null);
        }

        public int ExecuteNonQuery(string sql, CommandType commandType, IsolationLevel iso)
        {
            int num2;
            DbConnection connection = this.GetConnection();
            DbTransaction transaction = null;
            try
            {
                transaction = connection.BeginTransaction(iso);
                int num = ExecuteNonQuery(sql, commandType, connection, transaction, null);
                transaction.Commit();
                num2 = num;
            }
            catch
            {
                if (transaction != null)
                {
                    transaction.Rollback();
                }
                throw;
            }
            finally
            {
                this.RecycleConnection(connection);
            }
            return num2;
        }

        public int ExecuteNonQuery(string sql, CommandType commandType, DbParameter[] parameters)
        {
            int num;
            DbConnection connection = this.GetConnection();
            try
            {
                num = ExecuteNonQuery(sql, commandType, connection, null, parameters);
            }
            finally
            {
                this.RecycleConnection(connection);
            }
            return num;
        }

        public int ExecuteNonQuery(string sql, DbParameter[] parameters, IsolationLevel iso)
        {
            int num2;
            DbConnection connection = this.GetConnection();
            DbTransaction transaction = null;
            try
            {
                transaction = connection.BeginTransaction(iso);
                int num = ExecuteNonQuery(sql, CommandType.Text, connection, transaction, parameters);
                transaction.Commit();
                num2 = num;
            }
            catch
            {
                if (transaction != null)
                {
                    transaction.Rollback();
                }
                throw;
            }
            finally
            {
                this.RecycleConnection(connection);
            }
            return num2;
        }

        public int ExecuteNonQuery(string sql, DbConnection connection, DbTransaction transaction, DbParameter[] parameters)
        {
            return ExecuteNonQuery(sql, CommandType.Text, connection, transaction, parameters);
        }

        public int ExecuteNonQuery(string sql, CommandType commandType, DbConnection connection, DbTransaction transaction)
        {
            return ExecuteNonQuery(sql, commandType, connection, transaction, null);
        }

        public int ExecuteNonQuery(string sql, CommandType commandType, DbConnection connection, DbParameter[] parameters)
        {
            return ExecuteNonQuery(sql, commandType, connection, null, parameters);
        }

        public int ExecuteNonQuery(string sql, CommandType commandType, DbParameter[] parameters, IsolationLevel iso)
        {
            int num2;
            DbConnection connection = this.GetConnection();
            DbTransaction transaction = null;
            try
            {
                transaction = connection.BeginTransaction(iso);
                int num = ExecuteNonQuery(sql, commandType, connection, transaction, parameters);
                transaction.Commit();
                num2 = num;
            }
            catch
            {
                if (transaction != null)
                {
                    transaction.Rollback();
                }
                throw;
            }
            finally
            {
                this.RecycleConnection(connection);
            }
            return num2;
        }

        public int ExecuteNonQuery(string sql, CommandType commandType, DbConnection connection, DbTransaction transaction, DbParameter[] parameters)
        {
            DbCommand command = GetCommand(connection, sql, commandType, transaction);
            if ((parameters != null) && (parameters.Length > 0))
            {
                command.Parameters.AddRange(parameters);
            }
            return command.ExecuteNonQuery();
        }
        #endregion
        #region ExecuteReader
        public DbDataReader ExecuteReader(string sql, DbConnection connection)
        {
            return ExecuteReader(sql, CommandType.Text, connection, null, null);
        }

        public DbDataReader ExecuteReader(string sql, DbConnection connection, DbTransaction transaction)
        {
            return ExecuteReader(sql, CommandType.Text, connection, transaction, null);
        }

        public DbDataReader ExecuteReader(string sql, DbConnection connection, DbParameter[] parameters)
        {
            return ExecuteReader(sql, CommandType.Text, connection, null, parameters);
        }

        public DbDataReader ExecuteReader(string sql, CommandType commandType, DbConnection connection)
        {
            return ExecuteReader(sql, commandType, connection, null, null);
        }

        public DbDataReader ExecuteReader(string sql, DbConnection connection, DbTransaction transaction, DbParameter[] parameters)
        {
            return ExecuteReader(sql, CommandType.Text, connection, transaction, parameters);
        }

        public DbDataReader ExecuteReader(string sql, CommandType commandType, DbConnection connection, DbTransaction transaction)
        {
            return ExecuteReader(sql, commandType, connection, transaction, null);
        }

        public DbDataReader ExecuteReader(string sql, CommandType commandType, DbConnection connection, DbParameter[] parameters)
        {
            return ExecuteReader(sql, commandType, connection, null, parameters);
        }

        public DbDataReader ExecuteReader(string sql, CommandType commandType, DbConnection connection, DbTransaction transaction, DbParameter[] parameters)
        {
            DbCommand command = GetCommand(connection, sql, commandType, transaction);
            if ((parameters != null) && (parameters.Length > 0))
            {
                command.Parameters.AddRange(parameters);
            }
            return command.ExecuteReader();
        }
        #endregion
        #region ExecuteScalar
        public object ExecuteScalar(string sql)
        {
            object obj2;
            DbConnection connection = this.GetConnection();
            try
            {
                obj2 = ExecuteScalar(sql, CommandType.Text, connection, null, null);
            }
            finally
            {
                this.RecycleConnection(connection);
            }
            return obj2;
        }

        public object ExecuteScalar(string sql, DbConnection connection)
        {
            return ExecuteScalar(sql, CommandType.Text, connection, null, null);
        }

        public object ExecuteScalar(string sql, CommandType commandType)
        {
            object obj2;
            DbConnection connection = this.GetConnection();
            try
            {
                obj2 = ExecuteScalar(sql, commandType, connection, null, null);
            }
            finally
            {
                this.RecycleConnection(connection);
            }
            return obj2;
        }

        public object ExecuteScalar(string sql, IsolationLevel iso)
        {
            object obj3;
            DbConnection connection = this.GetConnection();
            DbTransaction transaction = null;
            try
            {
                transaction = connection.BeginTransaction(iso);
                object obj2 = ExecuteScalar(sql, CommandType.Text, connection, transaction, null);
                transaction.Commit();
                obj3 = obj2;
            }
            catch
            {
                if (transaction != null)
                {
                    transaction.Rollback();
                }
                throw;
            }
            finally
            {
                this.RecycleConnection(connection);
            }
            return obj3;
        }

        public object ExecuteScalar(string sql, DbParameter[] parameters)
        {
            object obj2;
            DbConnection connection = this.GetConnection();
            try
            {
                obj2 = ExecuteScalar(sql, CommandType.Text, connection, null, parameters);
            }
            finally
            {
                this.RecycleConnection(connection);
            }
            return obj2;
        }

        public object ExecuteScalar(string sql, DbConnection connection, DbTransaction transaction)
        {
            return ExecuteScalar(sql, CommandType.Text, connection, transaction, null);
        }

        public object ExecuteScalar(string sql, DbConnection connection, DbParameter[] parameters)
        {
            return ExecuteScalar(sql, CommandType.Text, connection, null, parameters);
        }

        public object ExecuteScalar(string sql, CommandType commandType, DbConnection connection)
        {
            return ExecuteScalar(sql, commandType, connection, null, null);
        }

        public object ExecuteScalar(string sql, CommandType commandType, IsolationLevel iso)
        {
            object obj3;
            DbConnection connection = this.GetConnection();
            DbTransaction transaction = null;
            try
            {
                transaction = connection.BeginTransaction(iso);
                object obj2 = ExecuteScalar(sql, CommandType.Text, connection, transaction, null);
                transaction.Commit();
                obj3 = obj2;
            }
            catch
            {
                if (transaction != null)
                {
                    transaction.Rollback();
                }
                throw;
            }
            finally
            {
                this.RecycleConnection(connection);
            }
            return obj3;
        }

        public object ExecuteScalar(string sql, CommandType commandType, DbParameter[] parameters)
        {
            object obj2;
            DbConnection connection = this.GetConnection();
            try
            {
                obj2 = ExecuteScalar(sql, commandType, connection, null, parameters);
            }
            finally
            {
                this.RecycleConnection(connection);
            }
            return obj2;
        }

        public object ExecuteScalar(string sql, DbParameter[] parameters, IsolationLevel iso)
        {
            object obj3;
            DbConnection connection = this.GetConnection();
            DbTransaction transaction = null;
            try
            {
                transaction = connection.BeginTransaction(iso);
                object obj2 = ExecuteScalar(sql, CommandType.Text, connection, transaction, parameters);
                transaction.Commit();
                obj3 = obj2;
            }
            catch
            {
                if (transaction != null)
                {
                    transaction.Rollback();
                }
                throw;
            }
            finally
            {
                this.RecycleConnection(connection);
            }
            return obj3;
        }

        public object ExecuteScalar(string sql, DbConnection connection, DbTransaction transaction, DbParameter[] parameters)
        {
            return ExecuteScalar(sql, CommandType.Text, connection, transaction, parameters);
        }

        public object ExecuteScalar(string sql, CommandType commandType, DbConnection connection, DbTransaction transaction)
        {
            return ExecuteScalar(sql, commandType, connection, transaction, null);
        }

        public object ExecuteScalar(string sql, CommandType commandType, DbConnection connection, DbParameter[] parameters)
        {
            return ExecuteScalar(sql, commandType, connection, null, parameters);
        }

        public object ExecuteScalar(string sql, CommandType commandType, DbParameter[] parameters, IsolationLevel iso)
        {
            object obj3;
            DbConnection connection = this.GetConnection();
            DbTransaction transaction = null;
            try
            {
                transaction = connection.BeginTransaction(iso);
                object obj2 = ExecuteScalar(sql, CommandType.Text, connection, transaction, parameters);
                transaction.Commit();
                obj3 = obj2;
            }
            catch
            {
                if (transaction != null)
                {
                    transaction.Rollback();
                }
                throw;
            }
            finally
            {
                this.RecycleConnection(connection);
            }
            return obj3;
        }

        public object ExecuteScalar(string sql, CommandType commandType, DbConnection connection, DbTransaction transaction, DbParameter[] parameters)
        {
            DbCommand command = GetCommand(connection, sql, commandType, transaction);
            if ((parameters != null) && (parameters.Length > 0))
            {
                command.Parameters.AddRange(parameters);
            }
            return command.ExecuteScalar();
        }
        #endregion
        #region 帮助方法
        public bool GetBoolean(DbDataReader reader, int index)
        {
            return (!(reader[index] is DBNull) && Convert.ToBoolean(reader[index]));
        }

        public bool GetBoolean(DbDataReader reader, string name)
        {
            return (!(reader[name] is DBNull) && Convert.ToBoolean(reader[name]));
        }


        public DateTime? GetDateTime(DbDataReader reader, int index)
        {
            return ((reader[index] is DBNull) ? null : new DateTime?(Convert.ToDateTime(reader[index])));
        }

        public DateTime? GetDateTime(DbDataReader reader, string name)
        {
            return ((reader[name] is DBNull) ? null : new DateTime?(Convert.ToDateTime(reader[name])));
        }

        public int? GetInt32(DbDataReader reader, int index)
        {
            return ((reader[index] is DBNull) ? null : new int?(Convert.ToInt32(reader[index])));
        }

        public int? GetInt32(DbDataReader reader, string name)
        {
            return ((reader[name] is DBNull) ? null : new int?(Convert.ToInt32(reader[name])));
        }


        public String GetString(DbDataReader reader, int index)
        {
            return ((reader[index] is DBNull) ? null : reader.GetString(index));
        }

        public String GetString(DbDataReader reader, string name)
        {
            return ((reader[name] is DBNull) ? null : reader.GetString(reader.GetOrdinal(name)));
        }

        public Decimal? GetDecimal(DbDataReader reader, int index)
        {
            return ((reader[index] is DBNull) ? null : new Decimal?(reader.GetDecimal(index)));
        }

        public Decimal? GetDecimal(DbDataReader reader, string name)
        {
            return ((reader[name] is DBNull) ? null : new Decimal?(reader.GetDecimal(reader.GetOrdinal(name))));
        }

        public Double? GetGetDouble(DbDataReader reader, int index)
        {
            return ((reader[index] is DBNull) ? null : new Double?(reader.GetDouble(index)));
        }

        public Double? GetDouble(DbDataReader reader, string name)
        {
            return ((reader[name] is DBNull) ? null : new Double?(reader.GetDouble(reader.GetOrdinal(name))));
        }
        public object TranslateNullToDBNull(object obj)
        {
            return ((obj == null) ? DBNull.Value : obj);
        }
        #endregion

    }
}
